/**
 * Sample Database
 * @author YUN
 */
package sample.provider;

import sample.provider.ddl.SQLiteTable;
import sample.provider.ddl.SQLiteView;
import android.net.Uri;
import android.provider.BaseColumns;

public class MyDatabase {

	static final String AUTHORITY = "sample.provider.MyDatabase";
	static final Uri BASE_CONTENT_URI = Uri.parse("content://" + AUTHORITY + "/");

	// PlayListsTable
	public static final class PlayLists extends SQLiteTable implements BaseColumns {
		static final String TABLE_NAME = "play_lists";
		static final Uri CONTENT_URI = Uri.withAppendedPath(BASE_CONTENT_URI, TABLE_NAME);
		static final String PLAY_LISTS_TYPE = "vnd.android.cursor.dir/vnd.reco.playlists";
		static final String PLAY_LISTS_ITEM_TYPE = "vnd.android.cursor.item/vnd.reco.playlists";

		// columns
		/** type:Text */
		public static final String DISPLAY_NAME = "display_name";

		public PlayLists() {
			super(TABLE_NAME);
			addIntegerColumn(_ID, "PRIMARY KEY AUTOINCREMENT NOT NULL");
			addTextColumn(DISPLAY_NAME, "NOT NULL");
		}
	}

	// PlayListsView
	public static final class MembersInfo extends SQLiteView implements BaseColumns {
		static final String TABLE_NAME = "members_info";
		static final Uri CONTENT_URI = Uri.withAppendedPath(BASE_CONTENT_URI, TABLE_NAME);
		static final String PLAY_LISTS_MEMBERS_TYPE = "vnd.android.cursor.dir/vnd.reco.playlists/members";

		// columns
		/** type:Text */
		public static final String DISPLAY_NAME = "display_name";
		/** type:Integer */
		public static final String MEMBER_COUNT = "member_count";
		/** type:Integer */
		public static final String SORT_ORDER = "sort_order";

		public MembersInfo() {
			super(TABLE_NAME);
			addSelect(_ID, "p.%s", PlayLists._ID);
			addSelect(DISPLAY_NAME, "p.%s", PlayLists.DISPLAY_NAME);
			addSelect(MEMBER_COUNT, "count(m.%s)", PlayLists._ID);
			addSelect(SORT_ORDER, "max(m.%s)", Members.SORT_ORDER);

			setFrom("(SELECT %s" + ",%s" 
			        + " FROM %s) AS p LEFT OUTER JOIN %s AS m ON p.%s = m.%s", 
					        PlayLists._ID, 
					        PlayLists.DISPLAY_NAME, 
					        PlayLists.TABLE_NAME, 
					        Members.TABLE_NAME, 
					        PlayLists._ID,
							Members.PLAYLISTS_ID);

			setGroupBy("p.%s, p.%s", PlayLists._ID, PlayLists.DISPLAY_NAME);
		}
	}

	// MembersTable
	public static final class Members extends SQLiteTable implements BaseColumns {
		static final String TABLE_NAME = "members";
		static final Uri CONTENT_URI = Uri.withAppendedPath(BASE_CONTENT_URI, TABLE_NAME);
		static final String MEMBERS_TYPE = "vnd.android.cursor.dir/vnd.reco.members";
		static final String MEMBERS_ITEM_TYPE = "vnd.android.cursor.item/vnd.reco.members";

		// columns
		/** type:Integer */
		public static final String VIDEO_ID = "video_id";
		/** type:Integer */
		public static final String PLAYLISTS_ID = "playlists_id";
		/** type:Integer */
		public static final String SORT_ORDER = "sort_order";

		public static final Uri getContentUri(long playListsId) {
			return Uri.withAppendedPath(PlayLists.CONTENT_URI, "/" + playListsId + "/" + TABLE_NAME);
		}

		Members() {
			super(TABLE_NAME);
			addIntegerColumn(_ID, "PRIMARY KEY AUTOINCREMENT NOT NULL");
			addIntegerColumn(VIDEO_ID, "NOT NULL");
			addIntegerColumn(PLAYLISTS_ID, "NOT NULL");
			addIntegerColumn(SORT_ORDER, "NOT NULL");
		}
	}
}
